package com.github.eden.db;

import com.github.eden.config.Config;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class DbConnection {

    private Connection connection;

    public DbConnection(Config config) {
        try {
            Class.forName(config.getDbDriverClass());
            connection = DriverManager.getConnection(config.getDbUrl(), config.getDbUsername(), config.getDbPassword());
        } catch (Throwable e) {
            throw new DbException(e);
        }
    }

    public List<DbTable> getTables() {
        try {
            List<DbTable> tables = new ArrayList<>();
            Statement statement = connection.createStatement();
            String sql = "show tables";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String tableName = resultSet.getString(1);
                String tableComment= getTableCommentByTableName(tableName);
                List<DbColumn> columns= getColumnsByTableName(tableName);
                tables.add(new DbTable(tableName, tableComment, columns));
            }
            statement.close();
            resultSet.close();
            return tables;
        } catch (Throwable e) {
            throw new DbException(e);
        }
    }

    /**
     * 获得某表的建表语句
     *
     * @param tableName
     * @return
     */
    private String getTableCommentByTableName(String tableName) {
        String comment = null;
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SHOW CREATE TABLE " + tableName);
            if (resultSet != null && resultSet.next()) {
                String ddl = resultSet.getString(2);
                comment = parseTableComment(ddl);
            }
            statement.close();
            resultSet.close();
        } catch (Throwable e) {
            throw new DbException(e);
        }
        if (StringUtils.isBlank(comment)) {
            throw new DbException("Database table='" + tableName + "'  must have comment.");
        }
        return comment;
    }

    private String parseTableComment(String all) {
        String comment;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }

    private List<DbColumn> getColumnsByTableName(String tableName) {
        List<DbColumn> columns = new ArrayList<>();
        try {
            String sql = "show full fields from " + tableName;
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            int keys = 0;
            while (resultSet.next()) {
                boolean isKey = false;
                if ("PRI".equalsIgnoreCase(resultSet.getString("KEY"))) {
                    keys++;
                    isKey = true;
                }
                String name = resultSet.getString("FIELD");
                String type = resultSet.getString("TYPE");
                if (type.indexOf("(") > 0) {
                    type = type.substring(0, type.indexOf("("));
                }
                String comment = resultSet.getString("COMMENT");
                if (StringUtils.isBlank(comment)) {
                    throw new DbException("Database column='" + name + " of table='" + tableName + "'  must have comment.");
                }
                String isNull = resultSet.getString("NULL");

                DbColumn column = new DbColumn();
                column.setIsPrimaryKeyColumn(isKey);
                column.setColumnName(name);
                column.setColumnType(type);
                column.setColumnComment(comment);
                column.setIsAllowNull("YES".equalsIgnoreCase(isNull));
                columns.add(column);
            }
            if (keys == 0) {
                throw new DbException("Database table='" + tableName + "' not have primary key.");
            } else if (keys > 1) {
                throw new DbException("Database table='" + tableName + "' must have only one primary key.");
            }
            statement.close();
            resultSet.close();
        } catch (Exception e) {
            throw new DbException(e);
        }
        return columns;
    }

}
